"""
@Uses this program is created to post history table for all the tables
"""
import os
import MySQLdb
import sys
import xml.etree.cElementTree as ElementTree
reload(sys)
sys.setdefaultencoding('utf-8')

"""
site_file = "temp_sites"#"all_site_name"
table_type = "posthistory"
file_path = "D:\Research\ISEC-2014\data\content"
database = "isec2014"
user = "root"
password="123"
port=3306

"""
site_file = "all_site_name"
table_type = "posthistory"
file_path = "E:\Sangeeta\dataset\\2013_05_stack_overflow\Content"
database = "sangeeta"
user = "sangeetal"
password="sangeetal"
port=3307
#"""

db1= MySQLdb.connect(host="localhost", user=user, passwd=password,db=database, port=port)
insert_cursor = db1.cursor() 

file = open(site_file)
for line in file:
    site = line.strip() +".com"
    print  "site=",site
    table  = table_type+"_"+line.strip()+"_aug13"
    
    source = file_path+"\\"+site+"\\"+table_type+".xml"
    # get an iterable
    context = ElementTree.iterparse(source, events=("start", "end"))

    # turn it into an iterator
    context = iter(context)

    # get the root element
    event, root = context.next()
    count1 = 0 
    
    for event, entry in context:
        if event == "end" and entry.tag == "row":
            id= (str)(entry.attrib['Id'])
            #print "id=",id
            #... process record elements ...
            #print "hello"
            count=0
            Comment = ""
            Text = ""
            try:
                Comment=(str)(entry.attrib['Comment'])
                Comment = Comment.replace("\n"," ")
                Comment = Comment.replace("'"," ")
                Comment = Comment.replace("\""," ")
                Comment = Comment.replace(",", " ")
                Comment = Comment.replace("(", " ")
                Comment = Comment.replace(")", " ")
                Comment = Comment.replace("\\", " ")
            except Exception ,e:
                #print "error"            
                count = count+1
                #print e, "count1=", count
        
        
            try:
                Text=(str)(entry.attrib['Text']).replace("\r"," ")
                Text = Text.replace("\n"," ")
                Text = Text.replace("'"," ")
                Text = Text.replace("\""," ")
                Text = Text.replace(",", " ")
                Text = Text.replace("("," ")
                Text = Text.replace(")", " ")
                Text = Text.replace("\\", " ")
            except Exception, e:
                #print "error"
                count = count+1
            try:
                UserId=(str)(entry.attrib['UserId'])
                #print "userid=", UserId
            except Exception, e:
                UserId = "-1"
                count  =count+1 
            try:
                User_display_name=(str)(entry.attrib['UserDisplayName'])
                User_display_name = User_display_name.replace("'"," ")
            except Exception, e:
                User_display_name=""
                count = count+1
           
            insert_str = "insert into `"+ table+ "` values("+entry.attrib['Id']+","+entry.attrib['PostHistoryTypeId']+","+\
            entry.attrib['PostId']+",'"+entry.attrib['RevisionGUID']+"','"+entry.attrib['CreationDate']+"',"+\
            UserId+",'"+User_display_name+"','"+Comment+"','"+Text+"')"
            #print "insert=", insert_str
            insert_cursor.execute(insert_str)
        root.clear()
    
    db1.commit()   